Data Source Setups

The following specific connection details are needed for each type of data source in addition to the general ones described here.

Amazon Athena

  • Port: usually 444
  • Authentication: provide the application user and password
  • Region: select an AWS region from the list
  • S3 Staging Directory:

Amazon Redshift

  • Port: usually 5439
  • Authentication: provide the application user and password
  • Secure Connection: to secure connections ensure that the check box is enabled. If the certificate should also be validated, details need to be provided on how to find the certificate store on the host servers. This applies to all runtime, task and AI servers. Select the right elements needed for the certificate store and set the values.

    • Encrypted (SSL) - secure connections.

    • Validate Certificate - Check this to validate the server certificate.

    • Validate server certificate hostname - check to validate the hostname.

    • Use custom CA - provides the option to import a custom root CA certificate, or use a custom TrustStore.

Amazon S3

There is no need for a server address. The connection is built from the region and keys instead.

  • Region: select an AWS region from the list
  • Authentication: provide the AWS access Key ID and secret access keys

Apache Drill

The JDBC connectors for Drill needs to be uploaded manually using the Custom Connector tools. The driver can be found in the Connector Marketplace or is can be independently downloaded from Apache Drill.

  • Port: usually 31010
  • Authentication: provide the application user and password

Apache Hive

The JDBC connectors for Hive needs to be uploaded manually using the Custom Connector tools. The driver can be found in the Connector Marketplace or is can be independently downloaded from Apache Hive.

  • Port: usually 10000
  • Authentication: provide the application user and password

Apache Presto

  • Port: usually 8080
  • Authentication: provide the application user and password

Azure Blob

There is no need for a server address. The connection is built from the keys instead.

  • Authentication: provide the Azure account and keys

Azure Synapse

  • Instance Nameprovide the name of the instance if configured. This should be blank if using the default. This is not required if specifying a port.
  • Port: usually 1433
  • Authentication: provide the application user and password
  • Secure Connection: to secure connections ensure that the check box is enabled. If the certificate should also be validated, details need to be provided on how to find the certificate store on the host servers. This applies to all runtime, task and AI servers. Select the right elements needed for the certificate store and set the values.

    • Encrypted (SSL) - secure connections.

    • Validate Certificate - Check this to validate the server certificate. If its false, the connection will simply work with an unvalidated SSL certificate.

    • Override hostname in certificate hostname - ignore the certificate hostname and provide an alternative one.

    • TrustStore Settings - use this option to specify a custom TrustStore for the certificate if the certificate was not imported to the Java trust store.

    • Custom TrustStore - when using a custom trust store, provide the alternative trust store path and password.

BigQuery (Google)

There is no need for a server address. The connection is built from the OAuth code. For details on configuring user level data access to Google BigQuery consult the Data Access with OAuth Authentication page.

ClickHouse

  • Port: typically 8123
  • Authentication: provide the application user and password

Databricks

  • HTTP Path: as defined in the Databricks/ Spark control panel
  • Port: usually 443
  • Authentication: Based on an Access Token

Denodo

  • Port: default is 9999
  • Authentication: provide the application user and password

Exasol

  • Port: typically 8563
  • Authentication: provide the application user and password
  • Secure Connection: to secure connections ensure that the check box is enabled. If the certificate should also be validated, details need to be provided on how to find the certificate store on the host servers. This applies to all runtime, task and AI servers. Select the right elements needed for the certificate store and set the values.

    • Encrypted (SSL) - secure connections.

    • Validate Certificate - Check this to validate the server certificate.

    • Custom TrustStore - Use this option to specify a custom TrustStore for the certificate if the certificate was not imported to the Java trust store.

Facebook

Facebook requires a more complex process for setup. Click here for more details.

FireBird

  • Port: typically 3050
  • Default Database: The connection must have at least 1 database included to work.
  • Authentication: provide the application user and password

Glue (Datavard)

Datavard Glue needs to be installed into the host SAP system before the connection can be established.

  • URL: this is the REST API entry point from Glue
  • Authentication: provide the application user and password

Each intermediate Glue destination server (configured in Glue) needs to be mapped to its corresponding server in Pyramid. Both technologies need to be pointing at the same physical server and content for the connector to operate. Each Glue package is associated with a destination. The package will not be available unless that destination is added.

Google Analytics

There is no need for a server address. The connection is built from the OAuth code. For details on configuring user level data access to Google Analytics consult the Data Access with OAuth Authentication page.

Google Sheets

There is no need for administrative setup here, it can be setup from the Model interface directly. The sheets need to be transitioned to the "public" mode - where a simple URL provides access to the content.

Greenplum

  • Port: usually 6432
  • Authentication: provide the application user and password

IBM DB2

  • Port: typically 50000
  • Default Database: The connection must have at least 1 database included to work.
  • Authentication: provide the application user and password

IBM DB2 AS400

  • Default Database: The connection must have at least 1 database included to work.
  • Authentication: provide the application user and password

Impala

  • Port: typically 21050
  • Authentication: provide the application user and password

Pyramid In-Memory

  • Port: typically 12170
  • Authentication: provide the application user and password. The user is usually "default". Using different user names on the same address, will hard segment the data files and effectively create stand alone instances on the same host machine.

MariaDB

  • Port: typically 3306
  • Authentication: provide the application user and password

Mongo BI Connector

The JDBC connectors for Mongo needs to be uploaded manually using the Custom Connector tools. The driver can be found in the Connector Marketplace or is can be independently downloaded from Mongo.

This connector requires the enterprise edition of Mongo.

  • Port:typically 3307
  • Authentication: provide the application user and password

Microsoft OLAP (Multidimensional) & Tabular

  • Instance Nameprovide the name of the instance if configured. This should be blank if using the default. This is not required if specifying a port.
  • Portusually 2382
  • Authentication: provide the a domain user and password that has administrative rights to Analysis Services. Then set how end-user authentication will be accomplished.
    • Machine Fallback: check this in the event the security mechanics can be ignored
  • MDX Compatibility: set this to "1" for older MDX functional compliance.
  • Azure Tabular: indicate if this is an Azure Tabular connection (only on Tabular types)
  • Connection Type: choose the manner in which the SSAS server is configured to receive connections.
  • Multi Pass Authentication: if authentication via impersonation fails, try other authentication options.

Click here for more details about Analysis Services.

Microsoft SQL Server and Azure SQL Server

  • Instance Name: provide the name of the instance if configured. This should be blank if using the default. This is not required if specifying a port.
  • Port typically 1433
  • Authentication: provide the application user and password or provide Windows Authentication mechanism.
  • Secure Connection: to secure connections ensure that the check box is enabled. If the certificate should also be validated, details need to be provided on how to find the certificate store on the host servers. This applies to all runtime, task and AI servers. Select the right elements needed for the certificate store and set the values.

    • Encrypted (SSL) - secure connections.

    • Validate Certificate - check this to validate the server certificate.

    • Override hostname in certificate - ignore the hostname in the certificate.

    • TrustStore Settings - use this option to specify a custom trust store for the certificate if the certificate was not imported to the Java trust store.

    • Custom TrustStore - when using a custom trust store, provide the alternative trust store path and password.

Setting up Windows Authentication for MS SQL Server can be quite complex. Click here for more.

MySQL

  • Port: typically 3306
  • Authentication: provide the application user and password

Neo4J BI Connector

The JDBC connectors for Neo4J BI needs to be uploaded manually using the Custom Connector tools. The driver can be found in the Connector Marketplace or is can be independently downloaded from Neo4J .

  • Port: typically 7687
  • Default Database Name: neo4j
  • Authentication: provide the application user and password

Neo4J Cypher

Neo4j is not a relational database, and is not table-based. It uses its own query language to discover links between information sources.

  • Port: typically 7687
  • Authentication: provide the application user and password

Netezza

The JDBC connectors for Netezza needs to be uploaded manually using the Custom Connector tools. The driver can be found in the Connector Marketplace or is can be independently downloaded from IBM.

  • Port: typically 5480
  • Authentication: provide the application user and password

NetSuite

The JDBC connectors for NetSuite needs to be uploaded manually using the Custom Connector tools. The driver can be found in the Connector Marketplace or is can be independently downloaded from Oracle NetSuite.

  • Port: typically 1708
  • Authentication: provide the application user and password
  • Service Data Source: NetSuite.com
  • Host: provide a valid Host name
  • Account: provide a valid Account
  • Role: provide a valid Role

ODBC

Click here for more on configuring ODBC.

Oracle

  • Service Name: this is the separate Oracle service name or ID. Required.
  • Port: typically 1521
  • Authentication: provide the application user and password
  • Secure Connection: to secure connections ensure that the check box is enabled. If the certificate should also be validated, details need to be provided on how to find the certificate store on the host servers. This applies to all runtime, task and AI servers. Select the right elements needed for the certificate store and set the values.

    • Encrypted (SSL) - secure connections.

    • Validate server certificate DN - check this to validate the server certificate distinguished name.

    • Override DN in server certificate - ignore the distinguished name in the certificate. You can enter an alternative distinguished name.

    • Custom trust store - use this option to specify a custom trust store for the certificate if the certificate was not imported to the Java trust store. You can choose JKS or PKCS12 as type, then enter the network path and password for the server.

    • Custom Keystore - for client-based certificate authentication. Choose JKS or PKCS12 as type, then enter the network path and password for the server.

PostgreSQL

This connector will usually work for any implementation of PostgreSQL including Heroqu and Amazon Aurora.

  • Port: typically 5432
  • Authentication: provide the application user and password
  • Default Database: The connection may need at least 1 database included to work - it depends on the implementation.
  • Secure Connection: to secure connections ensure that the check box is enabled. If the certificate should also be validated, details need to be provided on how to find the certificate store on the host servers. This applies to all runtime, task and AI servers. Select the right elements needed for the certificate store and set the values.

    • Encrypted (SSL) - secure connections.

    • Validate Certificate - check this to validate the server certificate.

    • Validate server certificate hostname - check this to validate the hostname (which cannot be overridden). If this option is unchecked, the default Java TrustStore will be used to validate the certificate.

    • User custom certificate configuration - provide a custom root CA certificate.

SalesForce

You must provide the Consumer Key and Consumer Secret from your SalesForce environment. Click here to learn how to setup SalesForce for these details.

  • Authentication: provide the application user and password
  • Security Token: The security token for connections provided by SalesForce
  • Custom Domain: if required, the connection can be made to a custom SalesForce domain - which must then be provided.

SAP BW

SAP BW requires a more complex process for setup.

SAP HANA

SAP HANA requires a more complex process for setup.

SAP (Sybase) IQ

  • Instance Name: provide the name of the instance if configured. This should be blank if using the default.
  • Port typically 2638
  • Authentication: provide the application user and password.
  • Default Database: The connection needs at least 1 database included to work.

SharePoint

Note: Only SharePoint Lists are supported as data sources.

  • URL: this is the SharePoint entry point
  • Authentication: provide the Microsoft ID user and password (especially for Office 365) or provide the Windows Authentication mechanism.

SingleStore (MemSQL)

  • Port:typically 3306
  • Default Database: The connection must have at least 1 database included to work.
  • Authentication: provide the application user and password

Note: You can also use the MySQL connector for MemSQL.

Snowflake

Snowflake does not require a server address.

  • Account Name: the snowflake account name
  • Region: set the Snowflake region where your data warehouse is hosted
  • Authentication: provide the application user and password
  • Warehouse: Provide the name of the warehouse you will be connecting to. Each warehouse needs a separate connection.

 

  • For further details on configuring Single Sign On between Microsoft Azure AD and Snowflake, please see here.

Spark

  • Port usually 10000
  • Default Database: default
  • HTTP Path: sql/protocolv1/o/<account-id>/<cluster-id>
  • Authentication: provide the application user and password

For more information regarding Spark SQL, refer to Spark Connection Properties

Teradata

  • Port typically 1025
  • Authentication: provide the application user and password or use Keytabs

Twitter

Twitter requires a more complex process for setup. Click here for more details.

Vertica

  • Port typically 5433
  • Authentication: provide the application user and password.
  • Default Database: The connection needs at least 1 database included to work.